package cn.kgc.javaweb.dao.impl;

import cn.kgc.javaweb.dao.StudentDao;
import cn.kgc.javaweb.utils.DataSourceUtil;
import cn.kgc.javaweb.vo.StudentVO;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

/**
 * @Author: 课工场
 * @Version: v1.0  2023/5/25
 * @Description:
 */
public class StudentDaoImpl implements StudentDao {

    @Override
    public List<StudentVO> getList(Integer start, Integer limit, String studentName, String clazz) throws SQLException {

        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        /*
        *                           select *  from student limit ? , ?
        *   studentName             select *  from student  where name = ?  limit ? , ?
        *   clazz                   select *  from student  where clazz = ?  limit ? , ?
        *   clazz    studentName    select *  from student  where clazz = ?  and name = ?  limit ? , ?
        * */
        String sql = "select student.*  ,course.course_name  from student " +
                " left join course on student.course_id = course.id where 1=1 ";

        StringBuilder stringBuilder = new StringBuilder(sql);
        ArrayList<Object> params = new ArrayList<>();

        if(studentName!=null && !"".equals(studentName)){
            stringBuilder.append(" and  name like ?");   //  %a%
            params.add("%"+studentName+"%");
        }

        if(clazz!=null && !"".equals(clazz) ){
            stringBuilder.append(" and clazz like ? ");
            params.add("%"+clazz+"%");
        }

        // 拼接分页数据
        stringBuilder.append(" limit ? ,? ");
        params.add(start);
        params.add(limit);

        System.out.println(" page data  sql = " + stringBuilder.toString());
        System.out.println(" page  date  params = " + params);
        // 驼峰命名转换器  course_name   ->  courseName
        BasicRowProcessor basicRowProcessor = new BasicRowProcessor(new GenerousBeanProcessor());

        List<StudentVO> studentVOS = queryRunner.query(stringBuilder.toString(), new BeanListHandler<>(StudentVO.class,basicRowProcessor), params.toArray());
        return studentVOS;
    }

    @Override
    public Long getTotal(String studentName, String clazz) {

        QueryRunner queryRunner = new QueryRunner(DataSourceUtil.getDataSource());

        String sql = " select count(*) from student  where 1=1 ";
        StringBuilder stringBuilder = new StringBuilder(sql);

        ArrayList<Object> params = new ArrayList<>();

        if(studentName!=null && !"".equals(studentName)){
            stringBuilder.append(" and  name like ?  ");
            params.add("%"+studentName+"%");
        }

        if(clazz!=null && !"".equals(clazz)){
            stringBuilder.append(" and clazz like  ?  ");
            params.add("%"+clazz+"%");
        }

        System.out.println(" count sql = " + stringBuilder.toString());
        System.out.println(" count params = " + params);

        try {
            Long count = queryRunner.query(stringBuilder.toString(), new ScalarHandler<>(), params.toArray());
            return count;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return null;
    }
}
